package database;

import bean.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author asus
 */
public class db {
    public db() {
    }
    
    private static Connection con;
    private static Statement stmt;

    //setting connection
    public static void setConnection(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/popochat", "root", "");
            stmt = con.createStatement();
        } catch(Exception e){
            System.out.println("setting connection error");
            e.printStackTrace();
        }
    }

    //menutup connection
    public static void unsetConnection(){
        try {
            stmt.close();
            con.close();
        } catch (Exception e) {
            System.out.println("closing connection error");
            e.printStackTrace();
        }
    }

    //mendaftarkan user
    //mengembalikan 0 jika pendaftaran gagal
    public static int register(String nama, String username, String password, String email){
        String str = "INSERT INTO user(nama,username,password,email)" +
                "VALUES('"+nama+"','"+username+"','"+password+"','"+email+"')";
        int ret = 0;
        try{
            ret = stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
        return ret;
    }
    
    //menambah friend
    public static void addfriend(int id_follower, int id_followee){
        String str = "INSERT INTO friends(id_follower,id_followee)" +
                "VALUES("+id_follower+","+id_followee+")";
        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    /**ADMIN**/

    //melakukan ban terhadap user dengan id user_id
    public static void banlogin(int user_id){
        String str = "UPDATE user " +
                "SET ban = 1 " +
                "WHERE id="+user_id;
        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    //mencabut ban terhadap user dengan id user_id
    public static void unbanlogin(int user_id){
        String str = "UPDATE user " +
                "SET ban = 0 " +
                "WHERE id="+user_id;
        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    //menambah moderator
    public static void addmoderator(int user_id, int id_kategori){
        String str = "INSERT INTO moderator(id_user,id_kategori)" +
                "VALUES("+user_id+","+id_kategori+")";
        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    public static void delmoderator(int id){
        String str = "DELETE FROM moderator " +
                "WHERE id="+id;
        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }
    
    //menambah kategori baru
    public static int addcategory(String name, int parent_id){
        String str = "INSERT INTO kategori(name,parent_id)" +
                "VALUES('"+name+"',";
        int ret = 0;
        if(parent_id==0)
            str += "null)";
        else
            str += ""+parent_id+")";

        try{
            ret = stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
        return ret;
    }

    //menghapus kategori/subkategori
    public static void delcategory(int category_id){
        String str = "DELETE FROM kategori " +
                "WHERE id="+category_id;
        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    //mengubah kategori
    public static void changecategory(int category_id, String name, int parent_id){
        String str = "UPDATE kategori" +
                " SET name='"+name+"', ";
        if(parent_id==0)
            str+=" parent_id = null ";
        else
            str+=" parent_id="+parent_id;
        
        str+=" WHERE id="+category_id;

        try{
            stmt.executeUpdate(str);
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    /** SELECT **/

    /*
     * DARI ANA
     */
    //mengambil data user berdasarkan user_id
    public static user selectuser(int user_id){
        String str = "SELECT nama,username" +
                " FROM user" +
                " WHERE id="+user_id;
        user result = null;
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                String nama = rs.getString("nama");
                String username = rs.getString("username");

                result = new user(user_id, username, nama, 0);
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static user selectuser(String username){
        String str = "SELECT id,nama" +
                " FROM user" +
                " WHERE username='"+username+"'";
        user result = null;
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                String nama = rs.getString("nama");
                int user_id = rs.getInt("id");
                result = new user(user_id, username, nama, 0);
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static String ismoderator(int user_id){
        String result = null;
        int i = 0;
        String str = "SELECT name" +
                " FROM moderator, kategori" +
                " WHERE id_user="+user_id+" AND" +
                " id_kategori=kategori.id";
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                
                if(i==0){
                    result = "moderator pada kategori ";
                }
                else
                    result += ", ";

                String nama = rs.getString("name");
                result += nama;
                i++;
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
        if(i == 0){
            result = "user biasa";
        }
        return result;
    }

    public static int login(String username, String password) {
        String str = "SELECT *" +
                " FROM user" +
                " WHERE username='" + username + "' AND password='" + password + "'";
        
        int result = 0;
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                result = rs.getInt("id");
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
        return result;
    }

    // mengambil user id berdasarkan username
    public static int getUserId(String username) {
        String str = "SELECT id FROM user WHERE username='" + username + "'";
        int hasil = 0;
        try {
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                hasil = rs.getInt("id");
            }
        } catch (Exception e) {
            System.out.println("query execute error");
        }

        return hasil;
    }

    public static boolean isAdmin(String password) {
        boolean retval = false;

        String str = "SELECT *" +
        " FROM user" +
        " WHERE password='" + password + "' AND username='admin'";
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                retval = true;
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return retval;
    }

    public static boolean isBanned(int uid) {
        boolean retval = false;

        String str = "SELECT *" +
        " FROM user" +
        " WHERE id=" + uid + " AND ban=1";
        
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                retval = true;
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
        
        return retval;
    }

    public static String getUsername(int uid) {
        String retval = null;

        String str = "SELECT *" +
        " FROM user" +
        " WHERE id=" + uid;
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                retval = rs.getString("username");
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return retval;
    }
    
   /*
     * END DARI ANA
     */
    
    //mengembalikan list user yang merupakan friend dari user_id
    public static ArrayList<user> getfriendlist(int user_id){
        ArrayList<user> result = new ArrayList<user>();
        String str = "SELECT user.id,username" +
                " FROM user,friends" +
                " WHERE id_follower="+user_id+
                " AND user.id = id_followee";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");

                result.add(new user(id,username,null,0));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static ArrayList<moderator> getmoderators(){
        ArrayList<moderator> result = new ArrayList<moderator>();
        String str = "SELECT moderator.id,id_user,id_kategori,username,name" +
                " FROM moderator,user,kategori" +
                " WHERE moderator.id_user=user.id AND moderator.id_kategori=kategori.id";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                int id_user = rs.getInt("id_user");
                int id_kategori = rs.getInt("id_kategori");
                String username = rs.getString("username");
                String namakategori = rs.getString("name");

                result.add(new moderator(id, id_user, id_kategori, username, namakategori));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }
    
    public static ArrayList<user> getusers(){
        ArrayList<user> result = new ArrayList<user>();
        String str = "SELECT id,username" +
                " FROM user";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");

                result.add(new user(id,username,null,1));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }
    
    public static ArrayList<user> getuserbanned(){
        ArrayList<user> result = new ArrayList<user>();
        String str = "SELECT id,username" +
                " FROM user" +
                " WHERE ban=1";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");

                result.add(new user(id,username,null,0));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }
    
    public static ArrayList<user> getusernotbanned(){
        ArrayList<user> result = new ArrayList<user>();
        String str = "SELECT id,username" +
                " FROM user" +
                " WHERE ban=0";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");

                result.add(new user(id,username,null,0));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static ArrayList<category> getcategories(){
        ArrayList<category> result = new ArrayList<category>();
        String str = "SELECT * FROM kategori";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int parent_id = rs.getInt("parent_id");

                result.add(new category(id,name,parent_id));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static category getcategory(String name){
        category result = null;
        String str = "SELECT * FROM kategori WHERE name='"+name+"'";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                int parent_id = rs.getInt("parent_id");

                result = new category(id,name,parent_id);
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static category getparent(int id_child){
        category result = null;
        String str = "SELECT parent.*" +
                " FROM kategori as child, kategori as parent" +
                " WHERE child.id = "+id_child+" AND parent.id = child.parent_id";

        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int parent_id = rs.getInt("parent_id");

                result = new category(id,name,parent_id);
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static ArrayList<category> getchildren(int id_parent){
        ArrayList<category> result = null;
        String str = "SELECT child.*" +
                " FROM kategori as child, category as parent" +
                " WHERE parent.id = "+id_parent+" AND parent.id = child.parent_id";
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int parent_id = rs.getInt("parent_id");

                result.add(new category(id,name,parent_id));
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }

        return result;
    }

    public static int getmaxroom(){
        int result = 0;

        String str = "SELECT maxChat FROM user LIMIT 1";
        try{
            ResultSet rs = stmt.executeQuery(str);
            while (rs.next()) {
                result = rs.getInt("maxChat");
            }
        } catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
        return result;
    }

    public static void setmaxroom(int maxroom){
        String str = "UPDATE user SET maxChat="+maxroom;
        try{
            stmt.executeUpdate(str);
        }catch(Exception e){
            System.out.println("query execute error");
            e.printStackTrace();
        }
    }

    /**MAIN**/
    public static void main(String[] args){
        setConnection();
        System.out.println(ismoderator(2));
        unsetConnection();
    }
}
